package carassius.DAL;

import carassius.BLL.Events.Aquarium.AquariumAddedListener;
import carassius.BLL.Events.Aquarium.AquariumAddedPublisher;
import carassius.BLL.Events.Aquarium.AquariumRemovedListener;
import carassius.BLL.Events.Aquarium.AquariumRemovedPublisher;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class AquariumTable
		extends Table<AquariumRow, Integer>
		implements AquariumAddedPublisher,
				   AquariumRemovedPublisher
{
	//<editor-fold defaultstate="collapsed" desc="Fields">
	private final ArrayList<AquariumAddedListener> _aquariumAddedListeners;
	private final ArrayList<AquariumRemovedListener> _aquariumRemovedListeners;
	private boolean _allCached;
	private final String SQL_ADD = "INSERT INTO aquarium VALUES (NULL , ? , ? , ? , ? , ? , ? , ? , ? , ? )",
			SQL_DELETE = "DELETE FROM aquarium WHERE id = ?",
			SQL_GET_ALL = "SELECT * FROM aquarium",
			SQL_GET_BY_ID = "SELECT * FROM aquarium WHERE id = ?";
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Constructors">
	public AquariumTable()
	{
		_aquariumAddedListeners = new ArrayList<AquariumAddedListener>();
		_aquariumRemovedListeners = new ArrayList<AquariumRemovedListener>();
		_allCached = false;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Public methods">
	public AquariumRow add(String name, Integer length, Integer width,
						   Integer height, AquariumTypeRow type, String lighting,
						   String heating, String filtering, String co2) throws SQLException
	{
		return add(name, length, width, height, (type != null) ? type.getId() : null,
				lighting, heating, filtering, co2);
	}

	public AquariumRow add(String name, Integer length, Integer width,
						   Integer height, Integer type, String lighting,
						   String heating, String filtering, String co2) throws SQLException
	{
		Integer id = add(new Object[]
				{
					name, length, width, height, type, lighting, heating, filtering, co2
				});
		if (id >= 0)
		{
			return getByPrimaryKey(id);
		}
		return null;
	}

	@Override
	public Integer add(Object[] args) throws SQLException
	{
		Integer out = -1;
		if (args != null
				&& args.length == 9)
		{
			PreparedStatement st = Database.prepareStatement(SQL_ADD);
			st.setObject(1, (args[0] instanceof String) ? args[0] : null, Types.VARCHAR); //name
			st.setObject(2, (args[1] instanceof Integer) ? args[1] : null, Types.INTEGER); //length
			st.setObject(3, (args[2] instanceof Integer) ? args[2] : null, Types.INTEGER); //width
			st.setObject(4, (args[3] instanceof Integer) ? args[3] : null, Types.INTEGER); //height
			st.setObject(5, (args[4] instanceof AquariumTypeRow)
					? ((AquariumTypeRow) args[4]).getId()
					: (args[4] instanceof Integer) ? args[4] : null, Types.INTEGER); //type
			st.setObject(6, (args[5] instanceof String) ? args[5] : null, Types.VARCHAR); //lighting
			st.setObject(7, (args[6] instanceof String) ? args[6] : null, Types.VARCHAR); //heating
			st.setObject(8, (args[7] instanceof String) ? args[7] : null, Types.VARCHAR); //filtering
			st.setObject(9, (args[8] instanceof String) ? args[8] : null, Types.VARCHAR); //co2

			try
			{
				synchronized (getRows())
				{
					st.executeUpdate();
					ResultSet keys = st.getGeneratedKeys();

					if (keys.next())
					{
						out = keys.getInt(1);
						if (out >= 0)
						{
							_allCached = false;
							AquariumRow aquariumRow = getByPrimaryKey(out);
							_allCached = true;
							fireAquariumAddedEvent(aquariumRow);
						}
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return out;
	}

	@Override
	public boolean delete(Integer id) throws SQLException
	{
		boolean result = false;

		if (id != null && id >= 0)
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE);
			st.setInt(1, id);

			try
			{
				synchronized (getRows())
				{
					AquariumRow aquariumRow = getByPrimaryKey(id);
					Database.getWaterRefreshmentTable().deleteByAquariumId(id);
					Database.getWaterMeasurementTable().deleteByAquariumId(id);
					Database.getOrganismListTable().deleteByAquariumID(id);

					if (st.executeUpdate() == 1)
					{
						getRows().remove(id);
						fireAquariumRemovedEvent(aquariumRow);
						result = true;
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return result;
	}

	@Override
	public Collection<AquariumRow> getAll() throws SQLException
	{
		synchronized (getRows())
		{
			if (!_allCached)
			{
				PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

				try
				{
					ResultSet query = st.executeQuery();

					while (query.next())
					{
						put(new AquariumRow(query));
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	@Override
	public AquariumRow getByPrimaryKey(Integer id) throws SQLException
	{
		AquariumRow row = null;
		if (id != null && id >= 0)
		{
			synchronized (getRows())
			{
				row = getRows().get(id);
				if (row != null || _allCached)
				{
					return row;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ID);
				st.setInt(1, id);

				try
				{
					ResultSet query = st.executeQuery();
					if (query.next())
					{
						row = new AquariumRow(query);
						put(row);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return row;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Events">
	//<editor-fold defaultstate="collapsed" desc="Added">
	@Override
	public void addAquariumAddedEventListener(AquariumAddedListener listener)
	{
		_aquariumAddedListeners.add(listener);
	}

	@Override
	public void removeAquariumAddedEventListener(AquariumAddedListener listener)
	{
		_aquariumAddedListeners.remove(listener);
	}

	private void fireAquariumAddedEvent(AquariumRow row)
	{
		for (AquariumAddedListener listener : _aquariumAddedListeners)
		{
			listener.aquariumAdded(row);
		}
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Removed">
	@Override
	public void addAquariumRemovedEventListener(AquariumRemovedListener listener)
	{
		_aquariumRemovedListeners.add(listener);
	}

	@Override
	public void removeAquariumRemovedEventListener(
			AquariumRemovedListener listener)
	{
		_aquariumRemovedListeners.remove(listener);
	}

	private void fireAquariumRemovedEvent(AquariumRow row)
	{
		for (AquariumRemovedListener listener : _aquariumRemovedListeners)
		{
			listener.aquariumRemoved(row);
		}
	}
	//</editor-fold>
	//</editor-fold>
}
